import { getDatabase } from '../database/init'

// 用户行为类型
export interface UserBehavior {
  id: string
  userId: string
  action: string
  entityType: 'task' | 'suggestion' | 'search' | 'filter'
  entityId?: string
  metadata?: any
  timestamp: string
}

// 行为统计数据
export interface UserStats {
  totalTasks: number
  completedTasks: number
  completionRate: number
  averageTasksPerDay: number
  mostActiveHour: number
  mostUsedCategory: string
  mostUsedPriority: string
  averageTaskDuration: number // 从创建到完成的平均时间（小时）
  productivityTrends: Array<{
    date: string
    tasksCompleted: number
    tasksCreated: number
  }>
}

// 个性化偏好
export interface UserPreferences {
  preferredWorkingHours: number[]
  productiveDays: string[]
  commonTaskPatterns: Array<{
    category: string
    priority: string
    frequency: number
  }>
  procrastinationTendencies: {
    delayedTasks: number
    averageDelay: number // 天数
  }
  suggestionFeedback: Array<{
    suggestionType: string
    acceptanceRate: number
    avgRating: number
  }>
}

// 记录用户行为
export async function trackUserBehavior(behavior: Omit<UserBehavior, 'id' | 'timestamp'>) {
  try {
    const db = getDatabase()
    const id = generateId()
    const timestamp = new Date().toISOString()
    
    await db.query(`
      INSERT INTO user_behaviors (id, user_id, action, entity_type, entity_id, metadata, timestamp)
      VALUES ($1, $2, $3, $4, $5, $6, $7)
    `, [id, behavior.userId, behavior.action, behavior.entityType, behavior.entityId, 
        JSON.stringify(behavior.metadata), timestamp])
    
    return { success: true, id, timestamp }
  } catch (error) {
    console.error('记录用户行为失败:', error)
    return { success: false, error: '记录用户行为失败' }
  }
}

// 获取用户统计数据
export async function getUserStats(userId: string, days: number = 30): Promise<UserStats> {
  try {
    const db = getDatabase()
    const startDate = new Date()
    startDate.setDate(startDate.getDate() - days)
    
    // 获取任务统计
    const taskStats = await db.query(`
      SELECT 
        COUNT(*) as total_tasks,
        COUNT(CASE WHEN completed = true THEN 1 END) as completed_tasks,
        AVG(CASE 
          WHEN completed = true AND created_at IS NOT NULL AND updated_at IS NOT NULL 
          THEN EXTRACT(EPOCH FROM (updated_at::timestamp - created_at::timestamp))/3600 
        END) as avg_duration,
        MODE() WITHIN GROUP (ORDER BY category) as most_used_category,
        MODE() WITHIN GROUP (ORDER BY priority) as most_used_priority
      FROM tasks 
      WHERE user_id = $1 AND created_at >= $2
    `, [userId, startDate.toISOString()])
    
    // 获取每日生产力趋势
    const productivityTrends = await db.query(`
      SELECT 
        DATE(created_at) as date,
        COUNT(*) as tasks_created,
        COUNT(CASE WHEN completed = true THEN 1 END) as tasks_completed
      FROM tasks 
      WHERE user_id = $1 AND created_at >= $2
      GROUP BY DATE(created_at)
      ORDER BY date DESC
      LIMIT 30
    `, [userId, startDate.toISOString()])
    
    // 获取最活跃时间段
    const activeHours = await db.query(`
      SELECT 
        EXTRACT(HOUR FROM timestamp) as hour,
        COUNT(*) as activity_count
      FROM user_behaviors
      WHERE user_id = $1 AND timestamp >= $2
      GROUP BY EXTRACT(HOUR FROM timestamp)
      ORDER BY activity_count DESC
      LIMIT 1
    `, [userId, startDate.toISOString()])
    
    const stats = taskStats.rows[0]
    const totalTasks = parseInt(stats.total_tasks) || 0
    const completedTasks = parseInt(stats.completed_tasks) || 0
    
    return {
      totalTasks,
      completedTasks,
      completionRate: totalTasks > 0 ? (completedTasks / totalTasks) * 100 : 0,
      averageTasksPerDay: totalTasks / days,
      mostActiveHour: activeHours.rows[0]?.hour || 9,
      mostUsedCategory: stats.most_used_category || 'work',
      mostUsedPriority: stats.most_used_priority || 'P2',
      averageTaskDuration: parseFloat(stats.avg_duration) || 24,
      productivityTrends: productivityTrends.rows.map(row => ({
        date: row.date,
        tasksCompleted: parseInt(row.tasks_completed),
        tasksCreated: parseInt(row.tasks_created)
      }))
    }
  } catch (error) {
    console.error('获取用户统计失败:', error)
    // 返回默认统计数据
    return {
      totalTasks: 0,
      completedTasks: 0,
      completionRate: 0,
      averageTasksPerDay: 0,
      mostActiveHour: 9,
      mostUsedCategory: 'work',
      mostUsedPriority: 'P2',
      averageTaskDuration: 24,
      productivityTrends: []
    }
  }
}

// 获取用户偏好
export async function getUserPreferences(userId: string): Promise<UserPreferences> {
  try {
    const db = getDatabase()
    
    // 分析用户的工作时间偏好
    const workingHours = await db.query(`
      SELECT 
        EXTRACT(HOUR FROM timestamp) as hour,
        COUNT(*) as activity_count
      FROM user_behaviors
      WHERE user_id = $1 AND action IN ('task_complete', 'task_create')
      GROUP BY EXTRACT(HOUR FROM timestamp)
      HAVING COUNT(*) >= 3
      ORDER BY activity_count DESC
    `, [userId])
    
    // 分析任务模式
    const taskPatterns = await db.query(`
      SELECT 
        category,
        priority,
        COUNT(*) as frequency
      FROM tasks
      WHERE user_id = $1
      GROUP BY category, priority
      ORDER BY frequency DESC
      LIMIT 10
    `, [userId])
    
    // 分析拖延倾向
    const procrastination = await db.query(`
      SELECT 
        COUNT(CASE WHEN due_date < updated_at THEN 1 END) as delayed_tasks,
        AVG(CASE 
          WHEN due_date < updated_at AND due_date IS NOT NULL 
          THEN EXTRACT(DAY FROM (updated_at::timestamp - due_date::timestamp))
        END) as avg_delay
      FROM tasks
      WHERE user_id = $1 AND completed = true
    `, [userId])
    
    // 分析建议反馈
    const suggestionFeedback = await db.query(`
      SELECT 
        metadata->>'suggestionType' as suggestion_type,
        COUNT(*) as total_suggestions,
        COUNT(CASE WHEN action = 'suggestion_accept' THEN 1 END) as accepted,
        AVG(CAST(metadata->>'rating' as FLOAT)) as avg_rating
      FROM user_behaviors
      WHERE user_id = $1 AND entity_type = 'suggestion'
      GROUP BY metadata->>'suggestionType'
    `, [userId])
    
    const procrastinationStats = procrastination.rows[0] || {}
    
    return {
      preferredWorkingHours: workingHours.rows.map(row => parseInt(row.hour)).slice(0, 8),
      productiveDays: ['Monday', 'Tuesday', 'Wednesday', 'Thursday', 'Friday'], // 默认工作日
      commonTaskPatterns: taskPatterns.rows.map(row => ({
        category: row.category,
        priority: row.priority,
        frequency: parseInt(row.frequency)
      })),
      procrastinationTendencies: {
        delayedTasks: parseInt(procrastinationStats.delayed_tasks) || 0,
        averageDelay: parseFloat(procrastinationStats.avg_delay) || 0
      },
      suggestionFeedback: suggestionFeedback.rows.map(row => ({
        suggestionType: row.suggestion_type,
        acceptanceRate: row.total_suggestions > 0 ? 
          (parseInt(row.accepted) / parseInt(row.total_suggestions)) * 100 : 0,
        avgRating: parseFloat(row.avg_rating) || 0
      }))
    }
  } catch (error) {
    console.error('获取用户偏好失败:', error)
    // 返回默认偏好
    return {
      preferredWorkingHours: [9, 10, 11, 14, 15, 16],
      productiveDays: ['Monday', 'Tuesday', 'Wednesday', 'Thursday', 'Friday'],
      commonTaskPatterns: [],
      procrastinationTendencies: {
        delayedTasks: 0,
        averageDelay: 0
      },
      suggestionFeedback: []
    }
  }
}

// 生成智能建议上下文
export async function generateAIContext(userId: string): Promise<string> {
  try {
    const [stats, preferences] = await Promise.all([
      getUserStats(userId),
      getUserPreferences(userId)
    ])
    
    const context = `
用户行为分析：
- 任务完成率: ${stats.completionRate.toFixed(1)}%
- 平均每日任务数: ${stats.averageTasksPerDay.toFixed(1)}个
- 最活跃时间: ${stats.mostActiveHour}:00
- 常用分类: ${stats.mostUsedCategory}
- 常用优先级: ${stats.mostUsedPriority}
- 平均任务耗时: ${stats.averageTaskDuration.toFixed(1)}小时

用户偏好：
- 偏好工作时间: ${preferences.preferredWorkingHours.join(', ')}点
- 常见任务模式: ${preferences.commonTaskPatterns.slice(0, 3).map(p => `${p.category}-${p.priority}`).join(', ')}
- 拖延情况: ${preferences.procrastinationTendencies.delayedTasks}个延期任务，平均延期${preferences.procrastinationTendencies.averageDelay.toFixed(1)}天

近期生产力趋势：
${stats.productivityTrends.slice(0, 7).map(trend => 
  `${trend.date}: 创建${trend.tasksCreated}个，完成${trend.tasksCompleted}个`
).join('\n')}
    `.trim()
    
    return context
  } catch (error) {
    console.error('生成AI上下文失败:', error)
    return '用户数据不足，使用基础建议。'
  }
}

function generateId(): string {
  return Math.random().toString(36).substr(2, 9)
}
